package com.hy.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Created by yoka24443 on 2018-4-8.
 */
public class DBUtil {
    private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    private static final String MYSQL_URL = "jdbc:mysql://127.0.0.1:3306/school";
    private static final String MYSQL_USER = "root";
    private static final String MYSQL_PASSWORD = "";

    private static Connection conn = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    private static void init() throws Exception {
        if(conn == null || conn.isClosed()) {
            createConnection();
        }

        if(statement == null || statement.isClosed()) {
            createStatement();
        }
    }

    private static Connection createConnection() {
        try {
            //1.加载驱动
            Class.forName(MYSQL_DRIVER);
            //2.驱动管理器获取数据库链接
            conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    private static Statement createStatement() {
        try {
            //3.获取statement，执行sql
            statement = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return statement;
    }

    /**
     * 查询
     *
     * @param sql 查询语句
     * @return
     */
    public static ResultSet executeSql(String sql) {
        try {
            // 初始化
            init();
            //4.执行sql返回结果集
            resultSet = statement.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return resultSet;
    }

    /**
     * 添加、修改、删除
     * @param sql T-Sql
     * @return
     */
    public static int executeUpdate(String sql) {
        int effectCount = 0;

        try {
            // 初始化
            init();
            //4.执行sql
            effectCount = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return effectCount;
    }

    /**
     * 关闭数据库连接、声明、结果集
     */
    public static void close() {
        try {
            // close ResultSet
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }

            if (statement != null) {
                statement.close();
                statement = null;
            }

            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
